package henry.lucky.alphay.db;

import henry.lucky.alphay.Domain.Card;
import henry.lucky.alphay.Domain.Prize;
import henry.lucky.alphay.Domain.PrizeCard;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


public class DataBase {
	
	// private Connection conn;
//	private String path = this.getClass().getResource("db/data.mdb").getPath().substring(1);
//	private String URL = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ="+path;
	private String URL = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=c:\\data.mdb";	
	private Properties prop;	
	public DataBase() {
		super();
		// TODO Auto-generated constructor stub
		try {

			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			prop = new Properties();    
		    prop.put("charSet", "gbk");    //解决中文乱码
			// conn=DriverManager.getConnection(URL);
		} catch (Exception e) {
			// TODO: handle exception
			
			e.printStackTrace();
		}
	}

	public List<Prize> findPrize(String sql) {
		try {
			Connection conn = DriverManager.getConnection(URL,prop);
			Statement stmt = conn.createStatement();
			ResultSet resultSet = stmt.executeQuery(sql);
			List<Prize> prizes = prizeResultToPrize(resultSet);
			resultSet.close();
			stmt.close();
			conn.close();
			return prizes;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	public List<Prize> findCurrentLuckyPrize() {

		String sql = "select * from Prize order by LuckyIndex desc";
		return findPrize(sql);
	}

	public void deletePrize(int id){
		String sql="delete from Prize where id="+id+"";
		changeTableItem(sql);
	}
	public void changeTableItem(String sql) {

		try {
			Connection conn = DriverManager.getConnection(URL,prop);
			Statement stmt = conn.createStatement();	
			stmt.executeUpdate(sql);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int initLucky() {
		
		Connection conn = null;
		Statement stmt = null;
		try {
			conn=DriverManager.getConnection(URL);
			stmt = conn.createStatement();
			conn.setAutoCommit(false);
			stmt.execute("update Prize set CanType=0");
			stmt.execute("update Card set IsLuckied=0");
			stmt.execute("delete from PrizeCard");
			conn.commit();
			conn.setAutoCommit(true);
			stmt.close();
			conn.close();
			return 1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
				stmt.close();
				conn.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}			
			return -1;
		}
	}
	public List<PrizeCard> findPrizeCard(String sql){
		
		try {
			Connection conn = DriverManager.getConnection(URL,prop);
			Statement stmt = conn.createStatement();
			ResultSet resultSet = stmt.executeQuery(sql);
			List<PrizeCard> prizeCards= prizeCardResultToPrizeCard(resultSet);
			resultSet.close();
			stmt.close();
			conn.close();
			return prizeCards;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}
	public List<PrizeCard> findPrizeCards(){
		
		String sql="select * from PrizeCard";
		return findPrizeCard(sql);
		
	}
	public List<PrizeCard> findPrizeCardsByItem(String CardName,String PrizeName){
		String sql="select * from PrizeCard where CardName like '%"+CardName+"%' and PrizeName like '%"+PrizeName+"%'";	
		return findPrizeCard(sql);
	}
	public void insertPrizeCard(PrizeCard prizeCard){
		
		String sql="insert into PrizeCard(CardName,PrizeName) values('"+prizeCard.getCardName()+"','"+prizeCard.getPrizeName()+"')";
		changeTableItem(sql);
		
	}
	public void insertPrize(Prize prize) {

		String sql = "insert into Prize(PrizeName,Name,Number1,CycleIndex,Price,Person,LuckyIndex,CanType) "
				+ "values('"
				+ prize.getPrizeName()
				+ "','"
				+ prize.getName()
				+ "',"
				+ prize.getNumber()
				+ ","
				+ prize.getCycleIndex()
				+ ","
				+ prize.getPrice()
				+ ",'"
				+ prize.getPerson()
				+ "',"
				+ prize.getLuckyIndex()
				+ ","
				+ prize.getCanType() + ")";
		changeTableItem(sql);
	}

	public void updatePrize(Prize prize){
		String sql="update prize set PrizeName='"+prize.getPrizeName()+"', Name='"+prize.getName()+"', Number1="+prize.getNumber()+", CycleIndex="+prize.getCycleIndex()+", Price="+prize.getPrice()+", Person='"+prize.getPerson()+"', LuckyIndex="+prize.getLuckyIndex()+", CanType="+prize.getCanType()+" where id="+prize.getId()+"";
		changeTableItem(sql);
	}
	public void insertCard(Card card) {

		try {
			Connection conn = DriverManager.getConnection(URL);
			Statement stmt = conn.createStatement();
			String sql = "insert into Card(CardName,IsLuckied) values('"
					+ card.getCardName() + "'," + card.getIsLuckied() + ")";
			stmt.execute(sql);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void updateCard(Card card) {

		try {
			Connection conn = DriverManager.getConnection(URL);
			Statement stmt = conn.createStatement();
			String sql = "UPDATE Card SET IsLuckied=" + card.getIsLuckied()
					+ " WHERE CardName='" + card.getCardName() + "'";
			stmt.executeUpdate(sql);
			stmt.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
			System.err.println("updateCard(Card card)"+e.getMessage());
			e.printStackTrace();
		}
	}

	public void clearCard(){
		
		String sql="delete from Card";
		changeTableItem(sql);
	}
	public void deleteCard(Card card) {

		try {
			Connection conn = DriverManager.getConnection(URL);
			Statement stmt = conn.createStatement();
			String sql = "delete from Card where CardName='"
					+ card.getCardName() + "'";
			stmt.execute(sql);
			stmt.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	public void deleteCards(int minInt, int maxInt) {

		try {
			Connection conn = DriverManager.getConnection(URL);
			Statement stmt = conn.createStatement();
			for (int i = minInt; i < maxInt; i++) {
				Card card = new Card(String.valueOf("i"));
				String sql = "delete from Card where CardName="
						+ card.getCardName() + "";
				stmt.execute(sql);
			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}

	List<Card> findCard(String sql) {
		List<Card> cards = new ArrayList<Card>();
		try {
			Connection conn = DriverManager.getConnection(URL);
			Statement stmt = conn.createStatement();
			ResultSet resultSet = stmt.executeQuery(sql);
			cards = cardResultToCard(resultSet);
			resultSet.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return cards;
	}

	public List<Card> findCards() {

		String sql = "select * from Card";
		return findCard(sql);
	}

	public List<Card> findCardsByCanType(int IsLuckied) {

		String sql = "select * from Card where IsLuckied=" + IsLuckied + "";
		return findCard(sql);
	}

	private List<Card> cardResultToCard(ResultSet resultSet)
			throws SQLException {
		// TODO Auto-generated method stub
		List<Card> cards = new ArrayList<Card>();
		if (resultSet != null) {
			while (resultSet.next()) {
				Card card = new Card(resultSet.getString("CardName"));
				card.setIsLuckied(resultSet.getInt("IsLuckied"));
				cards.add(card);
			}
			return cards;
		}
		return null;
	}

	private List<PrizeCard> prizeCardResultToPrizeCard(ResultSet resultSet) throws SQLException{
		
		List<PrizeCard> prizeCardscards = new ArrayList<PrizeCard>();
		if (resultSet != null) {
			while (resultSet.next()) {
				PrizeCard prizeCard = new PrizeCard(resultSet.getString("CardName"), resultSet.getString("PrizeName"));
				prizeCardscards.add(prizeCard);
			}
			return prizeCardscards;
		}
		return null;
	}
	private List<Prize> prizeResultToPrize(ResultSet resultSet)
			throws SQLException {

		List<Prize> prizes = new ArrayList<Prize>();
		if (resultSet != null) {
			while (resultSet.next()) {

				Prize prize = new Prize(resultSet.getInt("id"),
				resultSet.getString("PrizeName"),
				resultSet.getString("Name"),
				resultSet.getInt("Number1"),
				resultSet.getInt("CycleIndex"),
				resultSet.getInt("Price"),
				resultSet.getString("person"),
				resultSet.getInt("LuckyIndex"));
				prize.setCanType(resultSet.getInt("CanType"));
				prizes.add(prize);
			}
			return prizes;
		}
		return null;

	}
}
